package com.neo.androidcommon.sqlitedb;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;

import com.neo.androidcommon.BaseActivity;
import com.neo.androidcommon.R;
import com.neo.androidcommon.sqlitedb.entity.Person;

import java.util.ArrayList;
import java.util.List;

import butterknife.BindView;
import butterknife.ButterKnife;

/**
 * Created by Administrator on 2018/9/7.
 */

public class SQLiteActivity extends BaseActivity implements View.OnClickListener {
    @BindView(R.id.btn_insert)
    Button btnInsert;
    @BindView(R.id.btn_query)
    Button btnQuery;
    @BindView(R.id.btn_update)
    Button btnUpdate;
    @BindView(R.id.btn_delete)
    Button btnDelete;

    private MyDBHelper myDBHelper;
    private SQLiteDatabase database;

    @Override
    public int intiLayout() {
        return R.layout.activity_sqlite;
    }

    @Override
    public void initView() {

        myDBHelper = new MyDBHelper(this);

        btnQuery.setOnClickListener(this);
        btnInsert.setOnClickListener(this);
        btnUpdate.setOnClickListener(this);
        btnDelete.setOnClickListener(this);

    }

    @Override
    public void initData() {

    }

    @Override
    protected void initListener() {


    }


    private int i = 0;
    private StringBuilder sb;

    @Override
    public void onClick(View v) {

        database = myDBHelper.getWritableDatabase();

        switch (v.getId()) {

            case R.id.btn_insert:
                i++;
                ContentValues values = new ContentValues();

                values.put("name", "呵呵-" + i);
                //参数依次是：表名，强行插入null值得数据列的列名，一行记录的数据
                database.insert("person", null, values);
                Toast.makeText(this, "插入完毕", Toast.LENGTH_SHORT).show();
                break;

            case R.id.btn_query:
                sb = new StringBuilder();
                //参数依次是:表名，列名，where约束条件，where中占位符提供具体的值，指定group by的列，进一步约束
                //指定查询结果的排序方式
                Cursor cursor = database.query("person", null, null, null, null, null, null);

                while (cursor.moveToNext()) {

                    int pid = cursor.getInt(cursor.getColumnIndex("personid"));
                    String name = cursor.getString(cursor.getColumnIndex("name"));
                    sb.append("id：" + pid + "：" + name + "\n");

                }
                cursor.close();

                Toast.makeText(this, "-->" + sb, Toast.LENGTH_SHORT).show();

                break;

            case R.id.btn_update:

                ContentValues valuesUpdate = new ContentValues();
                valuesUpdate.put("name", "嘻嘻--");
                database.update("person", valuesUpdate, "name = ?", new String[]{"呵呵-2"});

                Toast.makeText(this, "修改完毕", Toast.LENGTH_SHORT).show();
                break;

            case R.id.btn_delete:


                database.delete("person", "personid = ?", new String[]{"3"});
                Toast.makeText(this, "删除", Toast.LENGTH_SHORT).show();

                break;


            default:
                break;
        }


    }

    public void save(Person p) {
        SQLiteDatabase db = myDBHelper.getWritableDatabase();
        db.execSQL("INSERT  INTO person(name,phone) values (?,?)", new Object[]{p.getName(), p.getPhone()});
    }

    public void delete(Integer id) {
        SQLiteDatabase db = myDBHelper.getWritableDatabase();
        db.execSQL("DELETE FROM PERSON WHERE ID = ?", new Integer[]{id});

    }

    public void update(Person p) {

        SQLiteDatabase db = myDBHelper.getWritableDatabase();
        db.execSQL("UPDATE person SET name = ?,phone = ? WHERE personid = ?", new String[]{p.getName(), p.getPhone(), p.getId()});
    }

    public Person find(Integer id) {

        SQLiteDatabase db = myDBHelper.getWritableDatabase();

        Cursor cursor = db.rawQuery("SELECT * FROM person WHERE personid = ?", new String[]{id.toString()});

        while (cursor.moveToFirst()) {

            String personid = cursor.getString(cursor.getColumnIndex("personid"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String phone = cursor.getString(cursor.getColumnIndex("phone"));
            Person person = new Person();

            person.setName(name);
            person.setPhone(phone);
            person.setId(personid);
            return person;
        }
        cursor.close();
        return null;

    }


    public List<Person> getScrollData(int offset, int maResult) {
        List<Person> persons = new ArrayList<>();

        SQLiteDatabase db = myDBHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM person ORDER BY personid ASC LIMIT = ? , ?", new String[]{String.valueOf(offset), String.valueOf(maResult)});
        while (cursor.moveToNext()) {


            String personid = cursor.getString(cursor.getColumnIndex("personid"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String phone = cursor.getString(cursor.getColumnIndex("phone"));
            Person person = new Person();

            person.setName(name);
            person.setPhone(phone);
            person.setId(personid);
            persons.add(person);
        }
        cursor.close();
        return persons;
    }


    public long getCount() {
        SQLiteDatabase readableDatabase = myDBHelper.getReadableDatabase();

        Cursor cursor = readableDatabase.rawQuery("SELECT COUNT(*) FROM person ", null);

        boolean b = cursor.moveToFirst();

        long result = cursor.getLong(0);
        return result;

    }


    public void trasction() {

        SQLiteDatabase db = myDBHelper.getWritableDatabase();
        db.beginTransaction();

        try {

            db.execSQL("update person set amount = amount - 10 where personid = 1");
            db.execSQL("update person set amount = amonut + 10 where personid = 2");
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }


    }

}
